﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using BasicForms;
using Lists;
using GeneralLibs;
using DevExpress.Data;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraCharts;

namespace BI.BIKPI
{
    public partial class FrmNhatKyThuChiTienDaChieu : BasicForms.FrmSupportPrintableComponents
    {
        public FrmNhatKyThuChiTienDaChieu()
        {
            InitializeComponent();
        }

        DsBI dsQLyQuy = new DsBI();
        GridSummaryItem summaryItemMaxFreight;

        private void FrmNhatKyChiTien_Load(object sender, EventArgs e)
        {
            if (DesignMode) return;
            try
            {
                cmbDoiTuongChi.DataSource = DanhMucDoiTuongNghiepVu.Instance.DataView;
                cmbDoiTuongChi.DisplayColumn = "Ten";
                cmbDoiTuongChi.ValueColumn = "ID";
                cmbDoiTuongChi.AddVisibleColumn("Ma", "Mã");
                cmbDoiTuongChi.AddVisibleColumn("Ten", "Tên");

                cmbKyBaoCao.SelectedIndex = 19;

                dsQLyQuy.DoiTuongNghiepVu.Merge(DanhMucDoiTuongNghiepVu.Instance.DataView.ToTable());

                gridControlDsDoiTuongChi.DataSource = dsQLyQuy.DoiTuongNghiepVu;

                ucOneSeriesChart.GenChart("Theo chiều khách hàng", ViewType.Bar, " (ĐV: triệu VNĐ)");
            }
            catch (Exception ex)
            {
            }

            try
            {
                foreach (DevExpress.XtraGrid.Columns.GridColumn column in gridViewDsDoiTuongChi.Columns)
                {
                    column.OptionsFilter.AutoFilterCondition = DevExpress.XtraGrid.Columns.AutoFilterCondition.Contains;
                }
                foreach (DevExpress.XtraGrid.Columns.GridColumn column in gridView.Columns)
                {
                    column.OptionsFilter.AutoFilterCondition = DevExpress.XtraGrid.Columns.AutoFilterCondition.Contains;
                }
                //foreach (DataRow row in dsThongKeNhapXuatTon.VatTuHangHoa.Rows)
                //{
                //    row["Choose"] = false;
                //}
                chkChooseAllDoiTuong.Checked = false;
                rdoGroupByDoiTuong.Checked = true;
                FormatNumberInGridView(gridViewDsDoiTuongChi);
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
        }

        private void chkChooseAllDoiTuong_CheckedChanged(object sender, EventArgs e)
        {
            foreach (DataRow row in dsQLyQuy.DoiTuongNghiepVu.Rows)
            {
                row["Choose"] = chkChooseAllDoiTuong.Checked;
            }
        }

        private void cmbLoaiDoiTac_KeyUp(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Delete) cmbDoiTuongChi.EditValue = Guid.Empty;
        }
        private void cmbLoaiDoiTac_EditValueChanged(object sender, EventArgs e)
        {
            //if (!(cmbNhomVatTuHangHoa.EditValue is Guid) || (Guid)cmbNhomVatTuHangHoa.EditValue == Guid.Empty) return;
            try
            {
                //if (cmbDoiTuongChi.EditValue is Guid && (Guid)cmbDoiTuongChi.EditValue != Guid.Empty)
                //{
                //    //Dieu kien loc la ID = cmbNhomVatTuHangHoa.EditValue + ID cac nhom con.
                //    string filterNhomCon = "CONVERT('{" + cmbDoiTuongChi.EditValue.ToString() + "}','System.Guid'), ";
                //    DanhMucNhomVatTuHangHoa cacNhomCon = DanhMucNhomVatTuHangHoa.Instance.GetNhomVatTuHangHoaCon((Guid)cmbDoiTuongChi.EditValue);
                //    foreach (NhomVatTuHangHoa nhomCon in cacNhomCon)
                //    {
                //        filterNhomCon += "CONVERT('{" + nhomCon.ID + "}','System.Guid'), ";
                //        //filterNhomCon += string.Format(", '{0}'", nhomCon.ID);
                //    }
                //    if (filterNhomCon.EndsWith(", ")) filterNhomCon = filterNhomCon.Remove(filterNhomCon.Length - 2);

                //    dsThongKeNhapXuatTon.VatTuHangHoa.Clear();
                //    string filter = string.Format("NhomVatTuHangHoaID IN ({0}) AND TrangThai = 1 ", filterNhomCon);
                //    dsThongKeNhapXuatTon.VatTuHangHoa.Merge(DanhMucVatTuHangHoa.Filter(filter).DataView.ToTable());
                //}
                //else
                //{
                //    dsThongKeNhapXuatTon.VatTuHangHoa.Clear();
                //    dsThongKeNhapXuatTon.VatTuHangHoa.Merge(DanhMucVatTuHangHoa.GetDanhMucHangHoaDangSuDung().DataView.ToTable());
                //}
                //chkChooseAllDoiTuong.Checked = false;
                //chkChooseAllDoiTuong.Checked = true;
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            DateTime ngayBatDau = dtNgayBatDau.DateTime;
            DateTime ngayKetThuc = dtNgayKetThuc.DateTime;
            if(!(FrmThamSoBaoCao.CheckKyBaoCaoVaThoiDiemKhoaSo(dtNgayBatDau, dtNgayKetThuc))) return;
            ThoiDiemKhoaSo thoiDiemKhoaSoGanNhat = DanhMucThoiDiemKhoaSo.Instance.GetThoiDiemKhoaSoGanNhat(ngayKetThuc);

            BasicForms.FrmProgressBar.StartShowProgress();
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();

                string doiTuongCondition ="";
                
                doiTuongCondition = "E.ID IN (";
                //foreach (DataRow row in dsQLyQuy.DoiTuongNghiepVu.Rows)
                //{
                //    if (row["Choose"] is bool && (bool)row["Choose"])
                //    {
                //        doiTuongCondition += string.Format("CONVERT('{0}', System.Guid), ", (Guid)row["ID"]); 
                //    }
                //}
                foreach (DataRow row in dsQLyQuy.DoiTuongNghiepVu.Rows)
                {
                    if (row["Choose"] is bool && (bool)row["Choose"])
                    {
                        doiTuongCondition += string.Format("'{0}', ", (Guid)row["ID"]);
                    }
                }
                if (doiTuongCondition.EndsWith(", ")) 
                    doiTuongCondition = doiTuongCondition.Substring(0, doiTuongCondition.Length - 2) + ")";
                else doiTuongCondition = "1 = 0";

                dsQLyQuy.DetailNhatKythuChi.Clear();

                if(rdoThu.Checked)
                    cmd.CommandText = string.Format("SELECT E.Ma MaDoiTuong, E.Ten TenDoiTuong, A.NgayGhiSo , D.So, A.Ngay, A.DienGiai,  \n" +
                        "B.SoHieu SoHieuTaiKhoanNo, C.SoHieu SoHieuTaiKhoanCo, A.GiaTri  \n" +
                        "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu LIKE '1111' OR B.SoHieu LIKE '112%')\n" +
                        "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID AND (C.SoHieu NOT LIKE '111%' AND C.SoHieu NOT LIKE '112%')\n" +
                        "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                        "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                        "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc  \n" +
                        "       AND {0}  \n", doiTuongCondition);
                else
                    cmd.CommandText = string.Format("SELECT E.Ma MaDoiTuong, E.Ten TenDoiTuong, A.NgayGhiSo , D.So, A.Ngay, A.DienGiai,  \n" +
                        "B.SoHieu SoHieuTaiKhoanNo, C.SoHieu SoHieuTaiKhoanCo, A.GiaTri  \n" +
                        "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID \n" +
                        "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID \n" +
                        "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                        "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                        "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc AND (C.SoHieu LIKE '111%' OR C.SoHieu LIKE '112%') \n" +
                        "       AND {0}  \n", doiTuongCondition);

                cmd.Parameters.Clear();
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("@NgayKetThuc", ngayKetThuc);

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dsQLyQuy.DetailNhatKythuChi);
                gridControl.DataSource = dsQLyQuy.DetailNhatKythuChi;

                FormatNumberInGridView(gridView);
                gridView.BestFitColumns();

                if (rdoGroupByDoiTuong.Checked)
                {
                    if(rdoThu.Checked)
                        cmd.CommandText = string.Format("SELECT {1} Series1Name, SUM(A.GiaTri) Series1Value \n" +
                        "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu LIKE '1111' OR B.SoHieu LIKE '112%')\n" +
                        "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID AND (C.SoHieu NOT LIKE '111%' AND C.SoHieu NOT LIKE '112%')\n" +
                        "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                        "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                        "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc  \n" +
                        "       AND {0} GROUP BY {1} \n", doiTuongCondition, "E.Ma");
                    else
                        cmd.CommandText = string.Format("SELECT  {1} Series1Name, SUM(A.GiaTri) Series1Value \n" +
                        "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID \n" +
                        "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID \n" +
                        "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                        "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                        "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc AND (C.SoHieu LIKE '111%' OR C.SoHieu LIKE '112%') \n" +
                        "       AND {0} GROUP BY {1}  \n", doiTuongCondition, "E.Ma");
                }
                else if(rdoGroupByTKNo.Checked)
                {
                   if(rdoThu.Checked)
                    cmd.CommandText = string.Format("SELECT {1} Series1Name, SUM(A.GiaTri) Series1Value \n" +
                        "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu LIKE '1111' OR B.SoHieu LIKE '112%')\n" +
                        "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID AND (C.SoHieu NOT LIKE '111%' AND C.SoHieu NOT LIKE '112%')\n" +
                        "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                        "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                        "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc  \n" +
                        "       AND {0} GROUP BY {1} \n", doiTuongCondition, "B.SoHieu");
                    else
                    cmd.CommandText = string.Format("SELECT  {1} Series1Name, SUM(A.GiaTri) Series1Value \n" +
                        "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID \n" +
                        "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID \n" +
                        "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                        "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                        "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc AND (C.SoHieu LIKE '111%' OR C.SoHieu LIKE '112%') \n" +
                        "       AND {0} GROUP BY {1}  \n", doiTuongCondition, "B.SoHieu");
                }
                else if (rdoGroupByTKCo.Checked)
                {
                   if(rdoThu.Checked)
                    cmd.CommandText = string.Format("SELECT {1} Series1Name, SUM(A.GiaTri) Series1Value \n" +
                        "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID AND (B.SoHieu LIKE '1111' OR B.SoHieu LIKE '112%')\n" +
                        "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID AND (C.SoHieu NOT LIKE '111%' AND C.SoHieu NOT LIKE '112%')\n" +
                        "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                        "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                        "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc  \n" +
                        "       AND {0} GROUP BY {1} \n", doiTuongCondition, "C.SoHieu");
                    else
                    cmd.CommandText = string.Format("SELECT  {1} Series1Name, SUM(A.GiaTri) Series1Value \n" +
                        "FROM ButToan A INNER JOIN TaiKhoan B ON A.TaiKhoanNoID = B.ID \n" +
                        "		INNER JOIN TaiKhoan C ON A.TaiKhoanCoID = C.ID \n" +
                        "		INNER JOIN ChungTu D ON A.ChungTuID = D.ChungTuID	 \n" +
                        "		INNER JOIN DoiTuongNghiepVu E ON D.DoiTuongNghiepVu1ID = E.ID			 \n" +
                        "WHERE  @NgayBatDau<= A.Ngay AND A.Ngay<=@NgayKetThuc AND (C.SoHieu LIKE '111%' OR C.SoHieu LIKE '112%') \n" +
                        "       AND {0} GROUP BY {1}  \n", doiTuongCondition, "C.SoHieu");
                }
                cmd.Parameters.Clear();
                dsQLyQuy.ChartThuChi.Clear();
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@NgayBatDau", ngayBatDau);
                cmd.Parameters.AddWithValue("@NgayKetThuc", ngayKetThuc);

                da.SelectCommand = cmd;
                da.Fill(dsQLyQuy.ChartThuChi);

                if (rdoGroupByDoiTuong.Checked)
                {
                    colGroup.FieldName = "TenDoiTuong";
                    colGroup.DisplayFormat.FormatString = "{0}";
                    if (colMa1.SortOrder == ColumnSortOrder.Ascending)
                        colMa1.SortOrder = ColumnSortOrder.Descending;
                    else
                        colMa1.SortOrder = ColumnSortOrder.Ascending;

                    colTKCo.SortOrder = ColumnSortOrder.None;
                    colTKNo.SortOrder = ColumnSortOrder.None;
                }
                else if (rdoGroupByTKNo.Checked)
                {
                    colGroup.FieldName = "SoHieuTaiKhoanNo";
                    colGroup.DisplayFormat.FormatString = "Số hiệu tài khoản: {0}";
                    if (colTKNo.SortOrder == ColumnSortOrder.Ascending)
                        colTKNo.SortOrder = ColumnSortOrder.Descending;
                    else
                        colTKNo.SortOrder = ColumnSortOrder.Ascending;

                    colTKCo.SortOrder = ColumnSortOrder.None;
                    colMa1.SortOrder = ColumnSortOrder.None;
                }
                else if (rdoGroupByTKCo.Checked)
                {
                    colGroup.FieldName = "SoHieuTaiKhoanCo";
                    colGroup.DisplayFormat.FormatString = "Số hiệu tài khoản: {0}";
                    if (colTKCo.SortOrder == ColumnSortOrder.Ascending)
                        colTKCo.SortOrder = ColumnSortOrder.Descending;
                    else
                        colTKCo.SortOrder = ColumnSortOrder.Ascending;
                    colMa1.SortOrder = ColumnSortOrder.None;
                    colTKNo.SortOrder = ColumnSortOrder.None;
                }

                gridView.GroupSummary.Clear();
                // Make the group footers always visible.
                gridView.GroupFooterShowMode = GroupFooterShowMode.VisibleIfExpanded;
                // Create and setup the first summary item in GroupMenu
                GridGroupSummaryItem item = new GridGroupSummaryItem();
                item.FieldName = "GiaTri";
                item.SummaryType = DevExpress.Data.SummaryItemType.Sum;
                item.DisplayFormat = " (Số phát sinh={0:### ### ### ###})";
                item.Tag = "GiaTri";
                gridView.GroupSummary.Add(item);

                // Create and setup the second summary item in SummaryBar
                GridGroupSummaryItem item1 = new GridGroupSummaryItem();
                item1.FieldName = "GiaTri";
                item1.SummaryType = DevExpress.Data.SummaryItemType.Sum;
                item1.DisplayFormat = "{0:### ### ### ###}";
                item1.ShowInGroupColumnFooter = colSoPhatSinh;
                gridView.GroupSummary.Add(item1);

                colSoPhatSinh.BestFit();


                //vẽ đồ thị
                ucOneSeriesChart.Data = dsQLyQuy.ChartThuChi;
                       
                ucOneSeriesChart.GenChart("Theo chiều khách hàng", ViewType.Bar, " (ĐV: triệu VNĐ)");
                 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                FormatNumberInGridView(gridViewDsDoiTuongChi);
                con.Close();
                con = null;
            }
            BasicForms.FrmProgressBar.FinishShowProgress();
        }

        private void btnPreview_Click(object sender, EventArgs e)
        {
            PrintWithHeaderAndFooter(false, gridControl, true, true, "Nhật ký chi tiền", "", "", "", "");
        }

        private void cmbKyBaoCao_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                FrmThamSoBaoCao.ThietLapKhoangThoiGianBaoCao(cmbKyBaoCao.Text, cmbKyBaoCao, dtNgayBatDau, dtNgayKetThuc);
            }
            catch (Exception ex)
            {
                BasicForms.FrmExceptionNotifier.ShowAndLog(ex);
            }
        }
    }
}
